-- **************************************************************************
--    Project Name:   全程时效-虚假发货监控明细表-平台上传运单整合
--    Job Name:       jms_dm__dm_sham_send_monitor_detail_dt_res_h9
--    Author :        季修魁
--    date：          2023/12/12
-- **************************************************************************
-- **************************************************************************


----------当天数据入表
insert overwrite table jms_dm.dm_sham_send_monitor_detail_dt_new partition (dt,ordersource_code)
select  bill_code
       ,input_time
       ,taking_time
       ,network_code
       ,network_name
       ,network_type
       ,agent_code
       ,agent_name
       ,fst_duty_type
       ,fst_duty_standard
       ,start_network_code
       ,start_network_name
       ,start_agent_code
       ,start_agent_name
       ,start_city_id
       ,start_city_desc
       ,start_provider_id
       ,start_provider_desc
       ,destination_network_code
       ,destination_network_name
       ,destination_agent_code
       ,destination_agent_name
       ,destination_city_id
       ,destination_city_desc
       ,destination_provider_id
       ,destination_provider_desc
       ,line_link_code
       ,line_link_name
       ,duty_org_code
       ,duty_org_name
       ,duration_hour
       ,deliver_network_code
       ,deliver_network_name
       ,deliver_city_id
       ,deliver_city_desc
       ,deliver_provider_id
       ,deliver_provider_desc
       ,sign_time
       ,is_reback
       ,fst_network_code
       ,fst_network_name
       ,fst_network_opt_time
       ,ordersource_name
       ,'' is_upload_flag
       ,'' upload_batch
       ,dt
       ,ordersource_code
  from jms_dm.dm_sham_send_monitor_detail_dt_mid
 where dt>=date_add('{{ execution_date | cst_ds }}',-14)
   and dt<='{{ execution_date | cst_ds }}'
distribute by dt,ordersource_code
;
---平台上传运单
with ship_upload_bill_tab as (
select *
  from (
select BILL_NO,UPLOAD_BATCH
       ,row_number() over (partition by BILL_NO order by CREATE_TIME desc ) as rn1
from jms_dwd.dwd_tab_false_ship_upload_det_dt
--from jms_dm.tab_false_ship_upload_det
 where dt>=date_add('{{ execution_date | cst_ds }}',-30)
   and dt<=date_add('{{ execution_date | cst_ds }}',1)
   ) t where rn1=1
),

----运单明细近35天,平台上传的
waybill_upload_bill_res as (
select t.waybill_no                         as waybill_no
       ,t.input_time                        as input_time
       ,t.taking_time                       as taking_time
       ,t.first_network_code                as network_code
       ,net1.name                           as network_name
       ,net1.network_type                   as network_type
       ,net1.agent_code                     as agent_code
       ,net1.agent_name                     as agent_name
       ,'始发网点停留超时'                  as fst_duty_type
       ,''                                  as fst_duty_standard
       ,t.first_network_code                as start_network_code
       ,net1.name                           as start_network_name
       ,net1.agent_code                     as start_agent_code
       ,net1.agent_name                     as start_agent_name
       ,net1.city_id                        as start_city_id
       ,net1.city_desc                      as start_city_desc
       ,net1.provider_id                    as start_provider_id
       ,net1.provider_desc                  as start_provider_desc
       ,t.end_network_code                  as destination_network_code
       ,net2.name                           as destination_network_name
       ,net2.agent_code                     as destination_agent_code
       ,net2.agent_name                     as destination_agent_name
       ,net2.city_id                        as destination_city_id
       ,net2.city_desc                      as destination_city_desc
       ,net2.provider_id                    as destination_provider_id
       ,net2.provider_desc                  as destination_provider_desc
       ,''                                  as line_link_code
       ,''                                  as line_link_name
       ,t.first_network_code                as duty_org_code
       ,net1.name                           as duty_org_name
	   ,''                                  as duration_hour
	   ,''                                  as deliver_network_code
	   ,''                                  as deliver_network_name
       ,''                                  as deliver_city_id
       ,''                                  as deliver_city_desc
       ,''                                  as deliver_provider_id
       ,''                                  as deliver_provider_desc
       ,t.sign_time                         as sign_time
       ,t.is_reback                         as is_reback
       ,t.first_network_code                as fst_network_code
       ,net1.name                           as fst_network_name
       ,''                                  as fst_network_opt_time
       ,case when ordersource_name='逍遥峰' then '逍遥峰'
             when ordersource_name='桃花岛' then '桃花岛'
             when ordersource_name='紫金山' then '紫金山'
             else '紫金山'
        end as ordersource_name
---       ,ordersource_name                    as ordersource_name
       ,if(upload.BILL_NO is not null,1,0)  as is_upload_flag
       ,upload.UPLOAD_BATCH                 as UPLOAD_BATCH
       ,to_date(taking_time)                as dt
       ,case when ordersource_code='D190' then 'D190'
             when ordersource_code='D09' then 'D09'
             when ordersource_code='D67' then 'D67'
             else 'D67' end                    as ordersource_code
  from (
select waybill_no                                                                                    as waybill_no
       ,input_network_code                                                                           as input_network_code
       ,input_time                                                                                   as input_time
       ,coalesce(taking_early_scantime,warehousing_early_scantime,input_time)                        as taking_time
       ,taking_early_scansitecode                                                                    as taking_early_scansitecode
       ,warehousing_early_scantime                                                                   as warehousing_early_scantime
       ,warehousing_early_scansitecode                                                               as warehousing_early_scansitecode
       ,coalesce(taking_early_scansitecode,warehousing_early_scansitecode,input_network_code)        as first_network_code
       ,dispatch_network_code                                                                        as end_network_code
       ,coalesce(sign_early_scantime,outstore_early_scantime)                                        as sign_time
       ,is_reback                                                                                    as is_reback
       ,first_center_network_code                                                                    as first_center_network_code
       ,end_center_network_code                                                                      as end_center_network_code
       ,taking_early_scansitecode                                                                    as taking_scansitecode
       ,ordersource_name
       ,ordersource_code
       ,dt                                                                                           as dt
  from jms_dwd.dwd_wide_unsign_summary_waybill_dt
 where dt>=date_add('{{ execution_date | cst_ds }}',-35)
   and dt<='{{ execution_date | cst_ds }}'
---   and ordersource_name in ('紫金山','桃花岛')
     ) t
  join ship_upload_bill_tab                         upload
    on t.waybill_no = upload.BILL_NO
  left join jms_dim.dim_network_whole_massage       net1
    on t.first_network_code = net1.code
  left join jms_dim.dim_network_whole_massage       net2
    on t.end_network_code = net2.code
),

--==明细结果
--任务跑出来的数据包含上传的单号是做标签
sham_send_monitor_detail_res1 as (
select  t1.bill_code
       ,t1.input_time
       ,t1.taking_time
       ,t1.network_code
       ,t1.network_name
       ,t1.network_type
       ,t1.agent_code
       ,t1.agent_name
       ,t1.fst_duty_type
       ,t1.fst_duty_standard
       ,t1.start_network_code
       ,t1.start_network_name
       ,t1.start_agent_code
       ,t1.start_agent_name
       ,t1.start_city_id
       ,t1.start_city_desc
       ,t1.start_provider_id
       ,t1.start_provider_desc
       ,t1.destination_network_code
       ,t1.destination_network_name
       ,t1.destination_agent_code
       ,t1.destination_agent_name
       ,t1.destination_city_id
       ,t1.destination_city_desc
       ,t1.destination_provider_id
       ,t1.destination_provider_desc
       ,t1.line_link_code
       ,t1.line_link_name
       ,t1.duty_org_code
       ,t1.duty_org_name
       ,t1.duration_hour
       ,t1.deliver_network_code
       ,t1.deliver_network_name
       ,t1.deliver_city_id
       ,t1.deliver_city_desc
       ,t1.deliver_provider_id
       ,t1.deliver_provider_desc
       ,t1.sign_time
       ,t1.is_reback
       ,t1.fst_network_code
       ,t1.fst_network_name
       ,t1.fst_network_opt_time
       ,t1.ordersource_name
       ,case when t2.network_code is not null then 1 else 0 end as is_upload_flag
       --任务跑出来的数据包含上传的单号是做标签
       ,t2.upload_batch                                                            as upload_batch
       ,t1.dt                                                                      as dt
       ,t1.ordersource_code                                                        as ordersource_code
  from jms_dm.dm_sham_send_monitor_detail_dt_new   t1       ---当日回刷数据
  left join (select * from waybill_upload_bill_res
              where dt>=date_sub('{{ execution_date | cst_ds }}',32)
                and dt<='{{ execution_date | cst_ds }}'
            ) t2
    on t1.bill_code= t2.waybill_no
 where t1.dt>=date_sub('{{ execution_date | cst_ds }}',32) and t1.dt<='{{ execution_date | cst_ds }}'
),

---上传的单号-任务跑出来的数据
sham_send_monitor_detail_res2 as (
select t1.waybill_no  as bill_code
       ,t1.input_time
       ,t1.taking_time
       ,t1.network_code
       ,t1.network_name
       ,t1.network_type
       ,t1.agent_code
       ,t1.agent_name
       ,t1.fst_duty_type
       ,t1.fst_duty_standard
       ,t1.start_network_code
       ,t1.start_network_name
       ,t1.start_agent_code
       ,t1.start_agent_name
       ,t1.start_city_id
       ,t1.start_city_desc
       ,t1.start_provider_id
       ,t1.start_provider_desc
       ,t1.destination_network_code
       ,t1.destination_network_name
       ,t1.destination_agent_code
       ,t1.destination_agent_name
       ,t1.destination_city_id
       ,t1.destination_city_desc
       ,t1.destination_provider_id
       ,t1.destination_provider_desc
       ,t1.line_link_code
       ,t1.line_link_name
       ,t1.duty_org_code
       ,t1.duty_org_name
       ,t1.duration_hour
       ,t1.deliver_network_code
       ,t1.deliver_network_name
       ,t1.deliver_city_id
       ,t1.deliver_city_desc
       ,t1.deliver_provider_id
       ,t1.deliver_provider_desc
       ,t1.sign_time
       ,t1.is_reback
       ,t1.fst_network_code
       ,t1.fst_network_name
       ,t1.fst_network_opt_time
       ,t1.ordersource_name
       ,'1'                          as  is_upload_flag
       ,t1.UPLOAD_BATCH
       ,t1.dt
       ,t1.ordersource_code
  from waybill_upload_bill_res  t1
  left join (select bill_code,dt from jms_dm.dm_sham_send_monitor_detail_dt_new
              where dt>=date_sub('{{ execution_date | cst_ds }}',32) and dt<='{{ execution_date | cst_ds }}'
			  ) t2
   on t1.waybill_no = t2.bill_code
 where t1.dt>=date_sub('{{ execution_date | cst_ds }}',32) and t1.dt<='{{ execution_date | cst_ds }}'
   and t2.bill_code is null
   )
--写入最终结果表
insert overwrite table jms_dm.dm_sham_send_monitor_detail_dt_new partition (dt,ordersource_code)
select  bill_code
       ,input_time
       ,taking_time
       ,network_code
       ,network_name
       ,network_type
       ,agent_code
       ,agent_name
       ,fst_duty_type
       ,fst_duty_standard
       ,start_network_code
       ,start_network_name
       ,start_agent_code
       ,start_agent_name
       ,start_city_id
       ,start_city_desc
       ,start_provider_id
       ,start_provider_desc
       ,destination_network_code
       ,destination_network_name
       ,destination_agent_code
       ,destination_agent_name
       ,destination_city_id
       ,destination_city_desc
       ,destination_provider_id
       ,destination_provider_desc
       ,line_link_code
       ,line_link_name
       ,duty_org_code
       ,duty_org_name
       ,duration_hour
       ,deliver_network_code
       ,deliver_network_name
       ,deliver_city_id
       ,deliver_city_desc
       ,deliver_provider_id
       ,deliver_provider_desc
       ,sign_time
       ,is_reback
       ,fst_network_code
       ,fst_network_name
       ,fst_network_opt_time
       ,ordersource_name
       ,is_upload_flag
       ,upload_batch
       ,dt
       ,ordersource_code
  from (select * from sham_send_monitor_detail_res1
         union all
		select * from sham_send_monitor_detail_res2
		) t
distribute by dt,ordersource_code
;
